/*
 * This file is part of Shopping Lists Organizer software
 *
 * Copyright (C) 2011 Denis Kvita <dkvita@gmail.com>
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, see <http://www.gnu.org/licenses/>.
 */

#include <QtGui>
#include <QtSql>

#include "purchase.h"

purchase::purchase(QObject *parent) :
	QAbstractTableModel(parent),lastShop(0),cc()
{
	fieldNames<<"pShop"<<"pSec"<<"pCat"<<"pTitle"<<"pPrcP"<<"pCntP"<<"costP"<<"pPrcB"<<"pCntB"<<"costB"<<"pJoy"<<"pDone";
}

int purchase::rowCount(const QModelIndex &parent) const
{
	return cc.count();
}

int purchase::columnCount(const QModelIndex &parent) const
{
	return 11;
}

QVariant purchase::data(const QModelIndex &index, int role) const
{
	if(role == Qt::DisplayRole || role == Qt::EditRole || role == Qt::CheckStateRole){
		if(role == Qt::CheckStateRole){
			if(index.column() == 3) return cc.value(index.row()).value(11).toInt() == 1 ? Qt::Checked : Qt::Unchecked;
		}else{
			if(index.column() == 6 || index.column() == 9){
				double n= cc.value(index.row()).value(index.column()-2).toDouble()*cc.value(index.row()).value(index.column()-1).toDouble();
				int r= (int)(n*1000);
				int rm= r%10;
				r= r/10;
				n= r;
				if(rm >= 5) n= n/100+0.01;
				else n= n/100;
				return n;
			}
			return cc.value(index.row()).value(index.column());
		}
	}
	if( (index.column() == 6 || index.column() == 9) && role == Qt::BackgroundRole ) return QBrush(QColor(255,255,0,128));
	return QVariant();
}

QVariant purchase::headerData(int section, Qt::Orientation orientation, int role) const
{
	if(orientation == Qt::Horizontal){
		if(role == Qt::DisplayRole){
			switch(section){
			case 0: return tr("shop");
			break;
			case 1: return tr("section");
			break;
			case 2: return tr("category");
			break;
			case 3: return tr("purchase");
			break;
			case 4: return tr("planning price");
			break;
			case 5: return tr("planning amount");
			break;
			case 6: return tr("planning cost");
			break;
			case 7: return tr("buying price");
			break;
			case 8: return tr("buying amount");
			break;
			case 9: return tr("buying cost");
			break;
			case 10: return tr("joy");
			break;
			default: return QAbstractTableModel::headerData(section, orientation, role);
			}
		}
	}else{
		;
	}
	return QAbstractTableModel::headerData(section, orientation, role);
}

bool purchase::setData(const QModelIndex &index, const QVariant &value, int role)
{
	QSqlDatabase dbc= QSqlDatabase::database("CN");
	QSqlQuery q(dbc);
	if(role == Qt::CheckStateRole && index.column() == 3){
		q.prepare(QString("UPDATE purchase SET pDone=:DN WHERE oid=:OID"));
		q.bindValue(":OID",rowid(index.row()));
		q.bindValue(":DN",value == Qt::Checked ? 1 : 0);
		if(!q.exec()) return false;
		cc[index.row()].setValue(11,value == Qt::Checked ? 1 : 0);
		emit dataChanged(index,index);
		return true;
	}
	if(value == cc.value(index.row()).value(index.column())) return true;
	QVariant v= value;
	if(role == Qt::EditRole || role == Qt::DisplayRole){
		int shopID= 0;
		if(index.column()==0){
			q.prepare(QString("SELECT oid FROM shop WHERE sTitle=:V"));
			q.bindValue(":V",value);
			if(!q.exec()) return reportErr(q);
			if(q.next()) shopID= q.value(0).toInt();
			else if(!value.toString().isEmpty()){
				q.prepare("INSERT INTO shop(sTitle) VALUES(:T)");
				q.bindValue(":T",value);
				if(!q.exec()) return reportErr(q);
				q.prepare("SELECT oid FROM shop WHERE sTitle=:V");
				q.bindValue(":V",value);
				if(!q.exec()) return reportErr(q);
				if(!q.next()) return reportErr(q);
				shopID= q.value(0).toInt();
			}
			v= shopID;
			lastShop= shopID;
		}
		q.prepare(QString("UPDATE purchase SET "+fieldNames.at(index.column())+"=:V WHERE oid=:OID"));
		q.bindValue(":OID",rowid(index.row()));
		q.bindValue(":V",v);
		if(!q.exec()) return reportErr(q);
		if(index.column() == 1) lastSec= v.toString();
		if(index.column() == 2) lastCat= v.toString();
		if(index.column() == 4 || index.column() == 5){
			emit totalPlanChanged(totalPlan());
			emit dataChanged(index,this->index(index.row(),6));
		}
		if(index.column() == 7 || index.column() == 8){
			emit totalBuyChanged(totalBuy());
			emit dataChanged(index,this->index(index.row(),9));
		}
	}
	cc[index.row()].setValue(index.column(),value);
	emit dataChanged(index,index);
	return true;
}

Qt::ItemFlags purchase::flags(const QModelIndex & index) const
{
	Qt::ItemFlags f= Qt::ItemIsEnabled|(index.column() == 6 || index.column() == 9 ? Qt::NoItemFlags: Qt::ItemIsSelectable|Qt::ItemIsEditable);
	if(index.column()==3) f|=Qt::ItemIsUserCheckable;
	return f;
}

bool purchase::insertRows(int row, int count, const QModelIndex & parent)
{
	if(count<=0) return false;
	beginInsertRows(parent, row, row+count-1);
	endInsertRows();
	return true;
}

bool purchase::removeRows(int row, int count, const QModelIndex & parent)
{
	if(count<=0) return false;
	beginRemoveRows(parent, row, row+count-1);
	endRemoveRows();
	return true;
}

void purchase::clear()
{
	beginResetModel();
	removeRows(0,rowCount());
	cc.clear();
	wList= QString();
	wDate= QDate();
	endResetModel();
	emit totalPlanChanged(0);
	emit totalBuyChanged(0);
}

void purchase::setList(const QString listName)
{
	wList= listName;
}

void purchase::restore(const QDate date, const QString listName)
{
	cc.clear();
	beginResetModel();
	removeRows(0,rowCount());
	wDate= date;
	wList= listName;

	QSqlDatabase dbc= QSqlDatabase::database("CN");
	QSqlQuery q(dbc);
	q.prepare("SELECT (SELECT sTitle FROM shop WHERE oid=purchase.pShop),pSec,pCat,pTitle,pPrcP"
			  ",pCntP,pPrcP*pCntP as costP,pPrcB,pCntB,pPrcB*pCntB as costB,pJoy,pDone,oid,"
			  "(SELECT sGeoLoc FROM shop WHERE oid=purchase.pShop) FROM purchase"
			  " WHERE pDate=:DT AND pList=:LST");
	q.bindValue(":DT", wDate.toString("yyyy-MM-dd"));
	q.bindValue(":LST", wList);
	q.exec();
	while(q.next()){
		cc.append(QSqlRecord(q.record()));
	}

	insertRows(0,rowCount());
	endResetModel();

	emit totalPlanChanged(totalPlan());
	emit totalBuyChanged(totalBuy());
}

QModelIndex purchase::create(QVariant shop, QVariant sec, QVariant ctg, QVariant title, QVariant planPrc, QVariant planCnt, QVariant buyPrc, QVariant buyCnt, QVariant joy, QVariant done)
{
	int r= rowCount();
	QSqlDatabase dbc= QSqlDatabase::database("CN");
	dbc.transaction();
	QSqlQuery q(dbc);
	q.prepare("INSERT INTO purchase(pDate,pList,pShop,pSec,pCat,pTitle,pPrcP,pCntP,pPrcB,pCntB,pJoy,pDone)"
			  " VALUES(:D,:L,:S,:SEC,:CAT,:T,:PP,:CP,:PB,:CB,:J,:DN)");
	q.bindValue(":D", wDate.toString("yyyy-MM-dd"));
	q.bindValue(":L",wList);
	q.bindValue(":SEC",sec);
	q.bindValue(":CAT",ctg);
	q.bindValue(":T",title);
	q.bindValue(":PP",planPrc);
	q.bindValue(":CP",planCnt);
	q.bindValue(":PB",buyPrc);
	q.bindValue(":CB",buyCnt);
	q.bindValue(":J",joy);
	q.bindValue(":DN",done);
	if(!q.exec()){
		dbc.rollback();
		return QModelIndex();
	}
	q.prepare("SELECT (SELECT sTitle FROM shop WHERE oid=purchase.pShop),pSec,pCat,pTitle,pPrcP"
			  ",pCntP,pPrcP*pCntP as costP,pPrcB,pCntB,pPrcB*pCntB as costB,pJoy,pDone,oid"
			  ",(SELECT sGeoLoc FROM shop WHERE oid=purchase.pShop) FROM purchase"
			  " WHERE oid=last_insert_rowid();");
	if(!q.exec() || !q.next()){
		dbc.rollback();
		return QModelIndex();
	}
	cc.append(QSqlRecord(q.record()));
	insertRows(r,1);
	dbc.commit();
	emit totalPlanChanged(totalPlan());
	emit totalBuyChanged(totalBuy());
	return QAbstractTableModel::index(r,0);
}

QModelIndex purchase::addEmpty()
{
	int r=rowCount();
	QSqlDatabase dbc= QSqlDatabase::database("CN");
	dbc.transaction();
	QSqlQuery q(dbc);
	q.prepare("INSERT INTO purchase(pDate,pList,pShop,pSec,pCat,pTitle,pPrcP,pCntP,pPrcB,pCntB,pJoy,pDone)"
			  " VALUES(:D,:L,:S,:SEC,:CAT,:T,:PP,:CP,:PB,:CB,:J,:DN)");
	q.bindValue(":D", wDate.toString("yyyy-MM-dd"));
	q.bindValue(":L",wList);
	q.bindValue(":S",lastShop);
	q.bindValue(":SEC",lastSec);
	q.bindValue(":CAT",lastCat);
	q.bindValue(":T","");
	q.bindValue(":PP",0);
	q.bindValue(":CP",0);
	q.bindValue(":PB",0);
	q.bindValue(":CB",1);
	q.bindValue(":J",0);
	q.bindValue(":DN",0);
	if(!q.exec()){
		dbc.rollback();
		reportErr(q);
		return QModelIndex();
	}
	q.prepare("SELECT (SELECT sTitle FROM shop WHERE oid=purchase.pShop),pSec,pCat,pTitle,pPrcP"
			  ",pCntP,pPrcP*pCntP as costP,pPrcB,pCntB,pPrcB*pCntB as costB,pJoy,pDone,oid"
			  ",(SELECT sGeoLoc FROM shop WHERE oid=purchase.pShop) FROM purchase "
			  "WHERE oid=last_insert_rowid();");
	if(!q.exec() || !q.next()){
		dbc.rollback();
		return QModelIndex();
	}
	cc.append(QSqlRecord(q.record()));
	dbc.commit();
	insertRows(r,1);
	emit totalPlanChanged(totalPlan());
	emit totalBuyChanged(totalBuy());
	return QAbstractTableModel::index(r,0);
}

bool purchase::rem(int row)
{
	if(rowid(row)<0) return false;
	QSqlDatabase dbc= QSqlDatabase::database("CN");
	QSqlQuery q(dbc);
	q.prepare(QString("DELETE FROM purchase WHERE oid=:OID"));
	q.bindValue(":OID",rowid(row));
	if(q.exec()){
		emit totalPlanChanged(totalPlan());
		emit totalBuyChanged(totalBuy());
		cc.removeAt(row);
		return removeRows(row,1);
	}
	return true;
}

int purchase::rowid(int row)
{
	if(cc.count()>row) return cc.value(row).value(12).toInt();
	return -1;
}

void purchase::print(QPrinter *printer, bool onlyUnchecked)
{
	QString txt="<p style=\"font-size:14pt;\">";
	QSqlDatabase dbc= QSqlDatabase::database("CN");
	QSqlQuery q(dbc);
	// get sections
	q.prepare("SELECT DISTINCT pSec FROM purchase WHERE pDate=:DT AND pList=:LST ORDER BY pSec ASC");
	q.bindValue(":DT", wDate.toString("yyyy-MM-dd"));
	q.bindValue(":LST", wList);
	q.exec();
	QStringList secs;
	while(q.next()) if(!q.value(0).toString().isEmpty()) secs.append(q.value(0).toString());

	foreach(QString sec, secs){
		if(onlyUnchecked){
			q.prepare("SELECT pTitle,sum(pCntP) FROM purchase WHERE pDate=:DT AND pList=:LST AND pSec=:SEC AND pDone<>1 GROUP BY pTitle ORDER BY pTitle ASC");
			q.bindValue(":DT", wDate.toString("yyyy-MM-dd"));
			q.bindValue(":LST", wList);
			q.bindValue(":SEC", sec);
		}else{
			q.prepare("SELECT pTitle,sum(pCntP) FROM purchase WHERE pDate=:DT AND pList=:LST AND pSec=:SEC GROUP BY pTitle ORDER BY pTitle ASC");
			q.bindValue(":DT", wDate.toString("yyyy-MM-dd"));
			q.bindValue(":LST", wList);
			q.bindValue(":SEC", sec);
		}
		q.exec();
		txt+= "<br><b><u>"+sec+"</u></b><br>";
		while(q.next()){
			txt+= q.value(0).toString()+" - "+q.value(1).toString()+"<br>\n";
		}
	}
	// print purchases with 'sec' set to NULL
	if(onlyUnchecked){
		q.prepare("SELECT pTitle,sum(pCntP) FROM purchase WHERE pDate=:DT AND pList=:LST AND (pSec ='' OR pSec IS NULL) AND pDone<>1 GROUP BY pTitle ORDER BY pTitle ASC");
		q.bindValue(":DT", wDate.toString("yyyy-MM-dd"));
		q.bindValue(":LST", wList);
	}else{
		q.prepare("SELECT pTitle,sum(pCntP) FROM purchase WHERE pDate=:DT AND pList=:LST AND (pSec ='' OR pSec IS NULL) GROUP BY pTitle ORDER BY pTitle ASC");
		q.bindValue(":DT", wDate.toString("yyyy-MM-dd"));
		q.bindValue(":LST", wList);
	}
	if(q.exec()){
		txt+= "<br><b><u>---</u></b><br>";
		while(q.next()){
			txt+= q.value(0).toString()+" - "+q.value(1).toString()+"<br>\n";
		}
	}

	txt+="</p>";

	QTextDocument *td= new QTextDocument(this);
	td->setHtml(txt);
	td->print(printer);
	delete td;
}

double purchase::totalBuy()
{
	QSqlDatabase dbc= QSqlDatabase::database("CN");
	QSqlQuery q(dbc);
	q.prepare("SELECT sum(pPrcB*pCntB) FROM purchase WHERE pDate=:DT AND pList=:LST");
	q.bindValue(":DT", wDate.toString("yyyy-MM-dd"));
	q.bindValue(":LST", wList);
	q.exec();
	if(q.next()) return q.value(0).toDouble();
	return 0;
}

double purchase::totalPlan()
{
	QSqlDatabase dbc= QSqlDatabase::database("CN");
	QSqlQuery q(dbc);
	q.prepare("SELECT sum(pPrcP*pCntP) FROM purchase WHERE pDate=:DT AND pList=:LST");
	q.bindValue(":DT", wDate.toString("yyyy-MM-dd"));
	q.bindValue(":LST", wList);
	q.exec();
	if(q.next()) return q.value(0).toDouble();
	return 0;
}

bool purchase::reportErr(QSqlQuery &q){
	qCritical()<< q.lastError().text();
	return false;
}

